<?php
// Check to ensure this file is included in Joomla!
defined('_JEXEC') or die( 'Restricted access' );

class PimModelProduct extends JModel {
	
	function __construct() {
		parent::__construct();
	}
	
	function count($where) {
		$query = "SELECT DISTINCT count(p.id)";
		$query.= " FROM #__product p ";
		$query.= "  JOIN #__serial as se ON(se.code = p.serial_code ) ";
		$query.= "  JOIN #__pond as po ON(po.code = p.pond_code ) ";
		$query.= "  JOIN #__fish_class as fc ON(fc.code = p.class_code ) ";
		$query.= "  JOIN #__customer as cu ON(cu.code = p.customer_code ) "; 
		$query.= "$where";
		
		$this->_db->setQuery($query);
		return $this->_db->loadResult();
	}
	
	function getData(&$total, &$dbrows , $order_by = 'id', $order_dir = 'DESC', $offset = 0, $limit = 30, $where = array()) {
		if(sizeof($where) > 0)
			$where = 'WHERE ' . implode(' AND ', $where);
		else
			$where = '';

		if($limit !== 0)
			$limit = "LIMIT $offset, $limit";
		else
			$limit = '';
			
		$query = "SELECT DISTINCT p.*, se.code as se_code, po.name as po_name, cu.name as cu_name,";
		$query.= " fc.name as fc_name, se.id as se_id, po.id as po_id, cu.id as cu_id, fc.id as fc_id";  
		$query.= " FROM #__product p ";
		$query.= "  JOIN #__serial as se ON(se.code = p.serial_code ) ";
		$query.= "  JOIN #__pond as po ON(po.code = p.pond_code ) ";
		$query.= "  JOIN #__fish_class as fc ON(fc.code = p.class_code ) ";
		$query.= "  JOIN #__customer as cu ON(cu.code = p.customer_code ) "; 
		$query.= "$where ORDER BY $order_by $order_dir $limit";

		$this->_db->setQuery($query);
		$dbrows = $this->_db->loadObjectList();

		$total = $this->count($where);
	}
	
	function getListStatus() {
		$arr_status = array(
			ManagerHelper::PRODUCT_STATUS_CAN_USE=>ManagerHelper::PRODUCT_STATUS_CAN_USE_TEXT,
			ManagerHelper::PRODUCT_STATUS_USED=>ManagerHelper::PRODUCT_STATUS_USED_TEXT,
			ManagerHelper::PRODUCT_STATUS_EXPIRED=>ManagerHelper::PRODUCT_STATUS_EXPIRED_TEXT
		);
		return $arr_status;		
	}
	
	function ExcelReport($objPHPExcel) {
		$query = $this->createQueryExport(); 

		$this->_db->setQuery($query);
		$rows = $this->_db->loadAssocList();
		
		$row_start = intval(ManagerHelper::ROW_START_EXPORT_EXCEL);
		
		//set title cell
        $objPHPExcel->getActiveSheet()->SetCellValue('A'.$row_start, 'Serial Code');
        $objPHPExcel->getActiveSheet()->SetCellValue('B'.$row_start, 'Pond Code');
        $objPHPExcel->getActiveSheet()->SetCellValue('C'.$row_start, 'Pond Name');
        $objPHPExcel->getActiveSheet()->SetCellValue('D'.$row_start, 'Customer Code');
        $objPHPExcel->getActiveSheet()->SetCellValue('E'.$row_start, 'Customer Name');
        $objPHPExcel->getActiveSheet()->SetCellValue('F'.$row_start, 'Fish Class Code');
        $objPHPExcel->getActiveSheet()->SetCellValue('G'.$row_start, 'Fish Class Name');
        $objPHPExcel->getActiveSheet()->SetCellValue('H'.$row_start, 'Feed Date');
        $objPHPExcel->getActiveSheet()->SetCellValue('I'.$row_start, 'Sale Date');
        $objPHPExcel->getActiveSheet()->SetCellValue('J'.$row_start, 'Expired Date');
        $objPHPExcel->getActiveSheet()->SetCellValue('K'.$row_start, 'Status');

        if (is_array($rows) && count($rows) > 0) {
            $row_data = $row_start + 1;
            foreach ($rows as $data) {
                $objPHPExcel->getActiveSheet()->SetCellValue('A' . $row_data, $data['se_code']);
                $objPHPExcel->getActiveSheet()->SetCellValue('B' . $row_data, $data['po_code']);
                $objPHPExcel->getActiveSheet()->SetCellValue('C' . $row_data, $data['po_name']);
                $objPHPExcel->getActiveSheet()->SetCellValue('D' . $row_data, $data['cu_code']);
                $objPHPExcel->getActiveSheet()->SetCellValue('E' . $row_data, $data['cu_name']);
                $objPHPExcel->getActiveSheet()->SetCellValue('F' . $row_data, $data['fc_code']);
                $objPHPExcel->getActiveSheet()->SetCellValue('G' . $row_data, $data['fc_name']);
                $objPHPExcel->getActiveSheet()->SetCellValue('H' . $row_data, ManagerHelper::formatDate($data['feed_date']));
                $objPHPExcel->getActiveSheet()->SetCellValue('I' . $row_data, ManagerHelper::formatDate($data['sale_date']));
                $objPHPExcel->getActiveSheet()->SetCellValue('J' . $row_data, ManagerHelper::formatDate($data['expired_date']));
                $objPHPExcel->getActiveSheet()->SetCellValue('K' . $row_data, ManagerHelper::getTextStatus($data['status']));
                
                $row_data++;
            }
        }
        
        return $objPHPExcel;
	}

	function PDFReport() {
		$query = $this->createQueryExport();

		$this->_db->setQuery($query);
		$rows = $this->_db->loadAssocList();

		$data  = '<table border="1" cellspacing="0" cellpadding="0">';
		$data .= '<tr style="background-color:#FFFF00; color:#0000FF; font-weight:bold; text-align:center">' ;		
		$data .= '<th>Serial Code</th>';
		$data .= '<th>Pond Code</th>';
		$data .= '<th>Pond Name</th>';
		$data .= '<th>Customer Code</th>';
		$data .= '<th>Customer Name</th>';
		$data .= '<th>Fish Class Code</th>';
		$data .= '<th>Fish Class Name</th>';
		$data .= '<th>Feed Date</th>';
		$data .= '<th>Sale Date</th>';
		$data .= '<th>Expired Date</th>';
		$data .= '<th>Status</th>';
		$data .= '</tr>';
		
		for($k=0; $k < count( $rows ); $k++) {
			$row = $rows[$k];
			
			$data .= '<tr style="text-align:center">';
			$data .= '<td>' . $row['se_code'] . '</td>';
			$data .= '<td>' . $row['po_code'] . '</td>';
			$data .= '<td>' . $row['po_name'] . '</td>';
			$data .= '<td>' . $row['cu_code'] . '</td>';
			$data .= '<td>' . $row['cu_name'] . '</td>';
			$data .= '<td>' . $row['fc_code'] . '</td>';
			$data .= '<td>' . $row['fc_name'] . '</td>';
			$data .= '<td>' . ManagerHelper::formatDate($row['feed_date']) . '</td>';
			$data .= '<td>' . ManagerHelper::formatDate($row['sale_date']) . '</td>';
			$data .= '<td>' . ManagerHelper::formatDate($row['expired_date']) . '</td>';
			$data .= '<td>' . ManagerHelper::getTextStatus($row['status']) . '</td>';
			$data .= '</tr>';
		}
		$data .= '</table>';
		
		return $data;		
	}
	
	function createQueryExport() {
		$mainframe = JFactory::getApplication();
		
		$where = $mainframe->getUserState('filter_products');
		$order_by = $mainframe->getUserStateFromRequest('products.filter_order', 'filter_order');
		$order_dir = $mainframe->getUserStateFromRequest('products.filter_order_Dir', 'filter_order_Dir');

		if (empty($where)) {
			$where = '';
		} else {
			$where = ' WHERE ' . implode(' AND ', $where);
		}
		
		if (empty($order_by)) {
			$order_by = 'p.id';
		}
		
		if (empty($order_dir)) {
			$order_dir = ManagerHelper::ORDER_DIR;
		}
		
		$query = "SELECT DISTINCT p.id as id, se.id as se_id, ";
		$query.= " se.code as se_code, po.name as po_name, cu.name as cu_name, fc.name as fc_name, ";
		$query.= " po.code as po_code, cu.code as cu_code, fc.code as fc_code, ";
		$query.= " p.feed_date as feed_date, p.sale_date as sale_date, p.expired_date as expired_date, p.status as status ";
		$query.= "  FROM #__product p ";
		$query.= "  JOIN #__serial as se ON(se.code = p.serial_code ) ";
		$query.= "  JOIN #__pond as po ON(po.code = p.pond_code ) ";
		$query.= "  JOIN #__fish_class as fc ON(fc.code = p.class_code ) ";
		$query.= "  JOIN #__customer as cu ON(cu.code = p.customer_code ) ";
		$query.= "$where ORDER BY $order_by $order_dir";
		
		return $query;
	}
}